package com.icim.util;

import static com.icim.define.CommDefine.DB_COMMIT_ERROR;
import static com.icim.define.CommDefine.DB_ROLLBACK_ERROR;
import static com.icim.define.CommDefine.GET_DB_GET_CONNECTION_ERROR;
import static com.icim.define.CommDefine.GET_DB_RECORD_CNT_ERROR;
import static com.icim.define.CommDefine.XML_NODE_TABLE_DBOPE_TIME_D;
import static com.icim.define.CommDefine.XML_NODE_EVT_TIMESTAMP;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import oracle.sql.TIMESTAMP;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.dom4j.Element;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;

import com.icim.common.JdbcConfig;
import com.icim.exception.LoadMainException;
import com.icim.table.Column;
import com.icim.table.Table;
import com.mysql.jdbc.Util;

@Service("dBUtil")
@Scope("prototype")
public class DBUtil {
	private static final String STRING_EMPTY = "";
	private Logger logger = Logger.getLogger(DBUtil.class);
	@Resource
	private JdbcConfig jdbcConfig;

	private ThreadLocal<Connection> connection = new ThreadLocal<Connection>();

	public static void main(String[] args) throws SQLException,
			ClassNotFoundException {
		String a = "a,'\\//";
		System.out.println("a");
		a = a.replace("'", STRING_EMPTY).replace(",", STRING_EMPTY)
				.replace("\\", STRING_EMPTY).replace("/", STRING_EMPTY);
		System.out.println(a);
		// DBUtil dbUtil = new DBUtil();
		// // dbUtil.connection = dbUtil.dataSource.getConnection();
		// String driver = "com.mysql.jdbc.Driver";
		// // String driver = "org.gjt.mm.mysql.Driver";
		// // Class.forName(driver);
		//
		// // Create a connection to the database
		// String url = "jdbc:mysql://10.20.2.74:3306/iCIM_CX?userUnicode=true";
		// String username = "root";
		// String password = "Ivo123";
		// dbUtil.connection = DriverManager.getConnection(url, username,
		// password);
		// Statement stmt = dbUtil.connection.createStatement();
		// ResultSet rs = stmt.executeQuery("SELECT 1");
		// while (rs.next()) {
		// if (rs.getString("1").equals("1")) {
		//
		// }
		// }
		// Table table = dbUtil.getColumn("RET_PRD_INFO");
		// System.out.println(table.getTableName());
	}

	private Connection getNewConnection() throws LoadMainException {
		Connection connection = null;
		try {
			String driver = jdbcConfig.getDriverName();
			String url = jdbcConfig.getUrl();
			String user = jdbcConfig.getUserName();
			String password = jdbcConfig.getPassword();

			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new LoadMainException(1, "connection error",e);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			throw new LoadMainException(1, "connection error",e1);
		}// 加载Oracle驱动程序// 获取连接
		return connection;
	}

	public void openConnection() throws LoadMainException {
		String errorMessage = null;
		try {
			Connection conn = getNewConnection();
			conn.setAutoCommit(false);
			connection.set(conn);
			// }
		} catch (SQLException e) {
			errorMessage = Util.stackTraceToString(e);
			logger.error(errorMessage);
			e.printStackTrace();
			throw new LoadMainException(GET_DB_GET_CONNECTION_ERROR,
					"无法获取DB连接", e);
		}
	}

	public void closeConnection() throws LoadMainException {
		try {
			Connection conn = getCurrentConnection();
			if (conn != null) {
				conn.close();
				connection.set(null);
			}
		} catch (SQLException e) {
			throw new LoadMainException(GET_DB_GET_CONNECTION_ERROR,
					"无法关闭DB连接", e);
		}
	}

	/**
	 * 获取数据库的表定义(所有列，主键,表名，schema)
	 *
	 * @param tableName
	 * @param schema
	 * @param connection
	 * @return
	 * @throws SQLException
	 * @throws LoadMainException
	 */
	public Table getColumn(String tableName) throws LoadMainException {
		String schema = null;
		StringBuilder sb = new StringBuilder();
		Table table = null;
		Statement stmt = null;
		String dataType;
		String errorMessage = null;
		try {
			Connection connection = null;
			connection = getCurrentConnection();
			schema = jdbcConfig.getSchema();
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet colRs = metaData
					.getColumns(null, null, tableName, null);
			ResultSet prmRs = metaData.getPrimaryKeys(null, null, tableName);
			List<String> priKeyNameList = new ArrayList<String>();
			List<Column> priKeyList = new ArrayList<Column>();
			List<Column> columnList = new ArrayList<Column>();

			while (prmRs.next()) {
				String columnName = prmRs.getString("COLUMN_NAME");
				String tableSchem = prmRs.getString("TABLE_SCHEM");
				if(StringUtils.equalsIgnoreCase(tableSchem,schema)){
					priKeyNameList.add(columnName);
				}

			}

			while (colRs.next()) {
				String columnName = colRs.getString("COLUMN_NAME");
				String columnType = colRs.getString("TYPE_NAME");
				int datasize = colRs.getInt("COLUMN_SIZE");
				int digits = colRs.getInt("DECIMAL_DIGITS");
				int nullable = colRs.getInt("NULLABLE");
				String tableSchem = colRs.getString("TABLE_SCHEM");

				if(!StringUtils.equalsIgnoreCase(tableSchem,schema)){
					continue;
				}
				Column cl = new Column();
				cl.setColumnName(columnName);
				cl.setSchema(tableSchem);
				cl.setDataType(columnType);
				cl.setNullAble(nullable == 0 ? true : false);
				cl.setDataSize(datasize);
				for (String pkColumnName : priKeyNameList) {
					if (pkColumnName.equals(columnName)) {
						cl.setPrimary(true);
					}
				}
				if (cl.isPrimary()) {
					priKeyList.add(cl);
				}
				columnList.add(cl);
			}

			table = new Table();
			table.setSchema(schema);
			table.setTableName(tableName);
			table.setColumnList(columnList);
			table.setKeyColumnList(priKeyList);
			return table;
		} catch (Exception e) {
			throw new LoadMainException(GET_DB_RECORD_CNT_ERROR, "获取表结构时出错", e);
		}
	}

	public boolean hasRow(Element tableElement, Table table)
			throws LoadMainException {
		StringBuffer sql = new StringBuffer("select count(*) as rowCnt from ");
		sql.append(table.getTableName());
		String _and = " and ";
		Connection connection = null;
		int rowCnt = 0;
		try {
			connection = getCurrentConnection();
			List<Column> keyList = table.getKeyColumnList();
			sql.append(" where ");

			for (int i = 0; i < keyList.size(); i++) {
				Column column = keyList.get(i);
				sql.append(column.getColumnName());
				sql.append("=?");
				sql.append(_and);
			}
			sql.delete(sql.length() - _and.length(), sql.length());
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql.toString());
			for (int i = 0; i < keyList.size(); i++) {
				Column column = keyList.get(i);
				prepareValue(preparedStatement, i, tableElement, column);
			}
			ResultSet rs = preparedStatement.executeQuery();
			rs.next();
			rowCnt = rs.getInt("rowCnt");
		} catch (Exception e) {
			throw new LoadMainException(GET_DB_RECORD_CNT_ERROR,
					"获取DB是否存在记录时出错", e);
		}

		if (rowCnt > 0) {
			return true;
		}

		//todo：检查表中数据的时间是否在xml数据时间之前
		if(rowCnt > 0 && checkTime(tableElement,table)) return true;

		return false;

	}

	private boolean checkTime (Element tableElement, Table table) throws LoadMainException {
		StringBuffer sql_check = new StringBuffer("select * from ");
		sql_check.append(table.getTableName());
		String _and = " and ";
		Connection connection = null;
		int rowCnt = 0;
		try {
			connection = getCurrentConnection();
			List<Column> keyList = table.getKeyColumnList();
			sql_check.append(" where ");

			for (int i = 0; i < keyList.size(); i++) {
				Column column = keyList.get(i);
				sql_check.append(column.getColumnName());
				sql_check.append("=?");
				sql_check.append(_and);
			}
			sql_check.delete(sql_check.length() - _and.length(), sql_check.length());
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql_check.toString());
			for (int i = 0; i < keyList.size(); i++) {
				Column column = keyList.get(i);
				prepareValue(preparedStatement, i, tableElement, column);
			}
			ResultSet rs = preparedStatement.executeQuery();
			while(rs.next()){
				Timestamp table_timestamp = rs.getTimestamp("evt_timestamp");
				String xml_timestamp = Dom4JUtil.getXmlValue(tableElement, XML_NODE_EVT_TIMESTAMP);
				if(table_timestamp.before(Timestamp.valueOf(xml_timestamp))){
					return true;
				}
			}
		} catch (Exception e) {
			throw new LoadMainException(GET_DB_RECORD_CNT_ERROR,
					"获取DB是否存在记录时出错", e);
		}
		return false;
	}


	public void commit() throws LoadMainException {
		try {
			getCurrentConnection().commit();
		} catch (SQLException e) {
			throw new LoadMainException(DB_COMMIT_ERROR, "提交事务时出错", e);
		}
	}

	public void rollBack() throws LoadMainException {
		try {
			getCurrentConnection().rollback();
		} catch (SQLException e) {
			throw new LoadMainException(DB_ROLLBACK_ERROR, "回滚事务时出错", e);
		}
	}

	/**
	 * @throws LoadMainException
	 *             发送心跳时检查数据库连接
	 *
	 * @Title: checkMysqlDBConnection
	 * @Description:
	 * @param @return
	 * @return boolean
	 * @exception
	 */
	public void checkMysqlDBConnection() throws LoadMainException {
		Connection checkConnection = null;
		Statement stmt = null;
		String sql = "SELECT * 1";
		String errorMessage = null;
		try {
			checkConnection = getNewConnection();
			stmt = getCurrentConnection().createStatement();
			stmt.executeQuery(sql);
		} catch (Exception e) {
			errorMessage = Util.stackTraceToString(e);
			logger.error(errorMessage);
			throw new LoadMainException(GET_DB_GET_CONNECTION_ERROR,
					errorMessage, e);
		} finally {
			try {
				checkConnection.close();
				stmt.close();
			} catch (SQLException e) {
				logger.error("发送心跳时 关闭Statement或者关闭connetion时报错"
						+ e.getMessage());
				e.printStackTrace();
			}

		}
	}

	public Connection getCurrentConnection() {
		return connection.get() == null ? null : (Connection) connection.get();
	}

	public String toDateString(String value) {
		if (STRING_EMPTY.equals(value)) {
			// return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
			// .format(new Date());
			return null;
		} else if (value.length() > 19) {
			return value.substring(0, 19);
		} else {
			return value;
		}
	}

	public int toInt(String value) {
		if (STRING_EMPTY.equals(value)) {
			return 0;
		} else {
			return Integer.parseInt(value);
		}
	}

	public short toShort(String value) {
		if (STRING_EMPTY.equals(value)) {
			return 0;
		} else {
			return Short.parseShort(value);
		}
	}

	public float toFloat(String value) {
		if (STRING_EMPTY.equals(value)) {
			return 0f;
		} else {
			return Float.parseFloat(value);
		}
	}

	public double toDouble(String value) {
		if (STRING_EMPTY.equals(value)) {
			return 0.0;
		} else {
			return Double.parseDouble(value);
		}
	}

	public BigDecimal toBigDecimal(String value) {
		if (STRING_EMPTY.equals(value)) {
			return new BigDecimal(0.0);
		} else {
			return new BigDecimal(value);
		}
	}

	private String checkString(String value) {
		return StringUtils.isEmpty(value) ? null : value;
	}

	public void prepareValue(PreparedStatement preparedStatement, int index,
							 Element tableElement, Column column) throws SQLException {


		String dataType = column.getDataType();
		Element columnElement = tableElement.element(column.getColumnName()
				.toLowerCase());
		String value = STRING_EMPTY;

		if (columnElement != null) {
			value = columnElement.getTextTrim();
		}
		//logger.info("name:"+ column.getColumnName() + ", type:" + column.getDataType()+", value:" + value);
		int parameterIndex = index + 1;
		if (XML_NODE_TABLE_DBOPE_TIME_D
				.equalsIgnoreCase(column.getColumnName())) {
			preparedStatement.setDouble(parameterIndex,
					TimeUtil.getDoubleTime());
		}
//		else if("EVT_TIMESTAMP".equalsIgnoreCase(column.getColumnName())){
//			preparedStatement.setTimestamp(parameterIndex,TimeUtil.getCurrentTimestamp());
//		}
		else if ("varchar".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, checkString(value));
		} else if ("bigint".equalsIgnoreCase(dataType)) {
			preparedStatement.setInt(parameterIndex, toInt(value));
		} else if ("longtext".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, checkString(value));
		} else if ("datetime".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, toDateString(value));
		} else if ("int".equalsIgnoreCase(dataType)) {
			preparedStatement.setInt(parameterIndex, toInt(value));
		} else if ("tinyint".equalsIgnoreCase(dataType)) {
			preparedStatement.setInt(parameterIndex, toInt(value));
		} else if ("decimal".equalsIgnoreCase(dataType)) {
			preparedStatement
					.setBigDecimal(parameterIndex, toBigDecimal(value));
		} else if ("double".equalsIgnoreCase(dataType)) {
			preparedStatement.setDouble(parameterIndex, toDouble(value));
		} else if ("char".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, checkString(value));
		} else if ("float".equalsIgnoreCase(dataType)) {
			preparedStatement.setFloat(parameterIndex, toFloat(value));
		} else if ("date".equalsIgnoreCase(dataType)) {
			Date date = new Date(value);
			String dataStr = toDateString(value);
			if (StringUtils.isEmpty(value)) {
				preparedStatement.setTimestamp(parameterIndex, null);
			} else {
//				preparedStatement.setTimestamp(parameterIndex,
//						Timestamp.valueOf(dataStr));
//				Timestamp timestamp = new Timestamp(date.getTime());
//				preparedStatement.setTimestamp(parameterIndex,
//						timestamp);
				DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String dateStr = sdf.format(date);
				Timestamp ts = new Timestamp(System.currentTimeMillis());
				ts = Timestamp.valueOf(dateStr);
				preparedStatement.setTimestamp(parameterIndex, ts);
			}
		} else if ("timestamp".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, toDateString(value));
		} else if ("mediumtext".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, value);
		} else if ("smallint".equalsIgnoreCase(dataType)) {
			preparedStatement.setShort(parameterIndex, toShort(value));
		} else if ("text".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, checkString(value));
		} else if ("time".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, toDateString(value));
		} else if ("number".equalsIgnoreCase(dataType)) {
			preparedStatement.setFloat(parameterIndex, toFloat(value));
		} else if ("varchar2".equalsIgnoreCase(dataType)) {
			preparedStatement.setString(parameterIndex, checkString(value));
		} else if ("timestamp(0)".equalsIgnoreCase(dataType)||"timestamp(6)".equalsIgnoreCase(dataType)) {
			if (StringUtils.isEmpty(value)) {
				preparedStatement.setTimestamp(parameterIndex, null);
			} else {
				//String dataStr = toDateString(value);
				preparedStatement.setTimestamp(parameterIndex,
						Timestamp.valueOf(value));
			}

		} else if("INTEGER".equals(dataType)){
			preparedStatement.setInt(parameterIndex, toInt(value));
		}else {
			throw new SQLException("Unsupported SQL data type (" + dataType
					+ ")");
		}
	}

}
